#!/bin/bash

echo "修复数据库文件..."

# 创建正确路径的数据库文件
sudo -u tomcat sqlite3 /var/lib/tomcat9/webapps/ROOT/orders_system.db << 'EOF'
-- 创建用户表
CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT UNIQUE NOT NULL,
    password TEXT NOT NULL,
    email TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建订单表
CREATE TABLE IF NOT EXISTS orders (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    category TEXT NOT NULL,
    product_name TEXT NOT NULL,
    quantity INTEGER NOT NULL,
    order_date TEXT NOT NULL,
    rating REAL NOT NULL,
    is_valid INTEGER DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建用户商品评分表
CREATE TABLE IF NOT EXISTS user_item_ratings (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id TEXT NOT NULL,
    item_id TEXT NOT NULL,
    rating REAL NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 插入初始用户数据
INSERT OR IGNORE INTO users (username, password, email) VALUES 
('admin', 'admin123', 'admin@example.com'),
('user1', 'password1', 'user1@example.com'),
('user2', 'password2', 'user2@example.com');

-- 插入示例订单数据
INSERT OR IGNORE INTO orders (category, product_name, quantity, order_date, rating, is_valid) VALUES 
('电子产品', 'iPhone 15', 2, '2024-01-15', 4.8, 1),
('服装', '羽绒服', 1, '2024-01-16', 4.5, 1),
('食品', '有机苹果', 5, '2024-01-17', 4.2, 1),
('电子产品', 'MacBook Pro', 1, '2024-01-18', 4.9, 1),
('家居', '智能音箱', 3, '2024-01-19', 4.3, 1);

-- 插入示例评分数据
INSERT OR IGNORE INTO user_item_ratings (user_id, item_id, rating) VALUES 
('user1', 'iPhone 15', 4.8),
('user1', '羽绒服', 4.5),
('user1', 'MacBook Pro', 4.9),
('user2', 'iPhone 15', 4.7),
('user2', '有机苹果', 4.2),
('user2', '智能音箱', 4.3);
EOF

# 设置正确的权限
sudo chown tomcat:tomcat /var/lib/tomcat9/webapps/ROOT/orders_system.db
sudo chmod 664 /var/lib/tomcat9/webapps/ROOT/orders_system.db

echo "数据库文件已创建：/var/lib/tomcat9/webapps/ROOT/orders_system.db"
sudo ls -la /var/lib/tomcat9/webapps/ROOT/orders_system.db

# 重启Tomcat以重新加载数据库连接
sudo systemctl restart tomcat9

echo "Tomcat已重启，系统应该可以正常工作了！" 